"""
Copyright 2012 Wieser Dietmar, Margit Mutschlechner.

This file is part of Multimedia Social Networking Platform.

Multimedia Social Networking Platform is free software: 
you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Multimedia Social Networking Platform is distributed
in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Multimedia Social Networking Platform.  
If not, see <http://www.gnu.org/licenses/>.

"""

from msnp.logger import Logger
import traceback
from msnp.model.MediaModel import NetSynchChanges
from msnp.persistence.mysql import DBErrorHandler
from msnp.model import MediaModel

########
#tables#
########

TABLE_NET_SYNCH_CHANGES = "net_synch_changes"
TABLE_USER_LAST_NETSYNCH = "last_netsynch"

#####################++++############
#columns for net_synch_changes table#
#################++++################

COLUMN_NSC_ID = "id"
COLUMN_NSC_MEDIA_ID = "media_id"
COLUMN_NSC_GROUPNAME = "groupname"
COLUMN_NSC_OWNERNAME = "ownername"
COLUMN_NSC_USERNAME = "username"
COLUMN_NSC_TIMESTAMP = "timestamp"
COLUMN_NSC_PROCESSED = "processed"
COLUMN_NSC_SOURCE = "source"
COLUMN_NSC_RELEVANT = "relevant"


###################################
#columns for member_netsynch table#
###################################

COLUMN_LASTNS_ID = "id"
COLUMN_LASTNS_LAST_NETSYNCH_ID = "last_netsynch_id"
COLUMN_LASTNS_USERNAME = "username"
COLUMN_LASTNS_GROUPNAME = "groupname"
COLUMN_LASTNS_OWNERNAME = "ownername"


def executeQuery(database, cursor, query):
    """Executes the given query on the given database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        query: A String containing the query to be executed.
    """
    try:
        cursor.execute(query)
        database.commit()
    except:
        Logger.info("error while executing query: " + query)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UNKNOWN, traceback.format_exc())

############
## SELECT ##
############

# net_synch_changes

def selectNetSynchMediaIdsProcessedSince(database, cursor, schema, timestamp):
    """Selects all entries of the NetSynchChanges table that were processed after the given timestamp,
    i.e. where the value of the column "timestamp" is bigger than the given timestamp and the value of the column "processed" is 1.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        timestamp: A float value representing a certain timestamp.
        
    Returns:
        A list of NetSynchChanges objects is returned, or None if an error occurred.
    """
    try:
        query = __getQuerySelectMediaIdsProcessedSince(schema)
        cursor.execute(query, [timestamp])
        mediaIds = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            mediaId = int(result[0])
            mediaIds.append(mediaId)
        return mediaIds
    except:
        Logger.info("Error: unable to select net_synch_changes processed since " + str(timestamp))
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectNetSynchMediasFromPlayerSince(database, cursor, schema, lastProcessedId):
    """Selects all entries of the NetSynchChanges table that origin from the player and are newer than a given id,
    i.e. where the value of the column "source" is PLAYER and the value of the column "id" is bigger than the given id.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        lastProcessedId: An integer number representing the id of a certain entry in the NetSynchChanges table.
        
    Returns:
        A list of NetSynchChanges objects is returned, or None if an error occurred.
    """
    try:
        query = __getQuerySelectNetSynchMediasFromPlayerSince(schema)
        cursor.execute(query, [str(lastProcessedId), MediaModel.NetSynchChangeSource.PLAYER])
        return __selectNetSynchChanges(cursor)
    except:
        Logger.info("Error: unable to select synch_changes")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def selectNetSynchMediasFromNetworkProcessedSince(database, cursor, schema, lastProcessedId):
    """Selects all entries of the NetSynchChanges table that were inserted because of changes in the network,
    that were processed and that have a higher id than the given one,
    i.e. where the value of the column "source" is NETWORK, the value of the column "processed" is 1
    and the value of the column "id" is bigger than the given id.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        lastProcessedId: An integer number representing the id of a certain entry in the NetSynchChanges table.
        
    Returns:
        A list of NetSynchChanges objects is returned, or None if an error occurred.
    """
    try:
        query = __getQuerySelectMediasFromNetworkProcessedSince(schema)
        cursor.execute(query, [MediaModel.NetSynchChangeSource.NETWORK, lastProcessedId])
        return __selectNetSynchChanges(cursor)
    except:
        Logger.info("Error: unable to select net_synch_change media ids for group since id " + str(lastProcessedId))
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def __selectNetSynchChanges(cursor):
    """Selects specific entries of the NetSynchChanges table according to the cursor.
    
    Args:
        cursor: A Cursor object needed for the fetching of the result of the previously executed query.
        
    Returns:
        A list of NetSynchChanges objects is returned.
    """
    changes = []
    while 1:
        result = cursor.fetchone()
        if result == None:
            break
        id = int(result[0])
        media_id = int(result[1])
        groupname = result[2]
        ownername = result[3]
        username = result[4]
        timestamp = float(result[5])
        processed = True if int(result[6]) == 1 else False
        source = result[7]
        relevant = True if int(result[8]) == 1 else False
        c = NetSynchChanges(media_id, groupname, ownername, username, timestamp, processed, source, relevant, id)
        changes.append(c)
    return changes

def selectLastUpdateProcessedTimestamp(database, cursor, schema, groupname, ownername):
    """Selects the highest value of the column "timestamp" of all entries in the NetSynchChanges table
    that have the given group name and owner name.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
        
    Returns:
        A float containing the stored timestamp or -1 if an error occurred.
    """
    try:
        query = __getQuerySelectLastUpdateProcessedTimestamp(schema)
        cursor.execute(query, [groupname, ownername])
        result = cursor.fetchone()
        if result != None and len(result) > 0 and result[0] != None:
            return float(result[0])
    except:
        Logger.info("Error: unable to select last update processed timestamp")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return - 1

def selectLastNetSynchId(database, cursor, schema):
    """Selects the highest value of the column "id" of the table NetSynchChanges.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        
    Returns:
        An integer containing the highest id or -1 if an error occurred.
    """
    try:
        query = __getQuerySelectLastNetSynchId(schema)
        cursor.execute(query)
        result = cursor.fetchone()
        if result != None and len(result) > 0 and result[0] != None:
            return int(result[0])
    except:
        Logger.info("Error: unable to select last netsynch id")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return - 1

def selectNearestNetSynchIdForTimestamp(database, cursor, schema, timestamp):
    """Selects from the NetSynchChanges table the value of the column id
    where the value of the column timestamp is nearest to the given timestamp. 
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        timestamp: A float containing a certain timestamp.
    
    Returns:
        An integer containing the id is returned, or -1 if an error occurred.
    """
    try:
        query = __getQuerySelectNearestNetSynchIdForTimestamp(schema)
        cursor.execute(query, [timestamp])
        result = cursor.fetchone()
        if result != None and len(result) > 0 and result[0] != None and result[0] > -1:
            return int(result[0])
    except:
        Logger.info("Error: unable to select last netsynch id for username")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return - 1

def selectNetSynchIdForGroupInitial(database, cursor, schema, groupname, ownername):
    """Selects from the NetSynchChanges table the value of the column "id"
    that corresponds to the given group name and owner name and where the value of the column "source" is INITIAl.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
    
    Returns:
        An integer containing the id is returned, or -1 if an error occurred.
    """
    try:
        query = __getQuerySelectNetSynchIdForGroupAndSource(schema)
        cursor.execute(query, [groupname, ownername, MediaModel.NetSynchChangeSource.INITIAL])
        result = cursor.fetchone()
        if result != None and len(result) > 0 and result[0] != None and result[0] > -1:
            return int(result[0])
    except:
        Logger.info("Error: unable to select last netsynch id for username")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return - 1

# last_net_synch

def selectLastNetSynchIdForUserAndGroup(database, cursor, schema, username, groupname, ownername):
    """Selects from the LastNetSynch table the value of the column last_netsynch_id
    that corresponds to the given user name, group name and owner name. 
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String representing the user name.
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
    
    Returns:
        An integer containing the id or -1 if an error occurred.
    """
    try:
        query = __getQuerySelectLastNetSynchIdForUserAndGroup(schema)
        cursor.execute(query, [username, groupname, ownername])
        result = cursor.fetchone()
        if result != None and len(result) > 0 and result[0] != None:
            return int(result[0])
        return - 1
    except:
        Logger.info("Error: unable to select last netsynch id for username")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

############
## INSERT ##
############

# net_synch_changes

def insertNetSynchChange(database, cursor, schema, netSynchChange):
    """Inserts the given NetSynchChanges object into the NetSynchChanges table.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        netSynchChange: The NetSynchChanges object to be inserted.
        
    Returns:
        True is returned if the insertion was successful, False otherwise.
    """
    try:
        Logger.info("insert net synch change")
        query = __getQueryInsertNetSynchChange(schema)
        media_id = netSynchChange.getMediaId() if netSynchChange.getMediaId() != None else - 1
        groupname = netSynchChange.getGroupname() if netSynchChange.getGroupname() != None else ""
        ownername = netSynchChange.getOwnername() if netSynchChange.getOwnername() != None else ""
        username = netSynchChange.getUsername() if netSynchChange.getUsername() != None else ""
        timestamp = netSynchChange.getTimestamp()
        processed = "1" if netSynchChange.isProcessed() else "0"
        source = netSynchChange.getSource()
        relevant = "1" if netSynchChange.isRelevant() else "0"
        cursor.execute(query, [media_id, groupname, ownername, username, timestamp, processed, source, relevant])
        database.commit()
        return True
    except:
        Logger.info("Error: unable to insert net synch changes")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False

def setNetSychnChangeRelevantToFalse(database, cursor, schema):
    """Sets in the table NetSynchChanges the value of the column "relevant" to false for all existing entries.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        
    Returns:
        True is returned, if the update was successful, False otherwise.
    """
    try:
        query = __getQuerySetRelevantToFalse(schema)
        cursor.execute(query)
        database.commit()
        return True
    except:
        Logger.info("Error: unable to setting net synch changes to not relevant")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    return False
        
# last_netsynch

def setLastNetSynchIdForUserAndGroup(database, cursor, schema, username, groupname, ownername, id):
    """Updates or inserts in the LastNetSynch table the given id as last_netsynch_id for the given user name, group name and owner name.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        username: A String representing the user name.
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
        id: An integer representing the id to be inserted or updated.
        
    Returns:
        True is returned if the insertion or update was successful, False otherwise.
    """
    try:
        oldId = selectLastNetSynchIdForUserAndGroup(database, cursor, schema, username, groupname, ownername)
        if  oldId == None or oldId == -1:
            query = __getQueryInsertLastNetSynchIdForUserAndGroup(schema)
            #Logger.info(query)
            cursor.execute(query, [username, groupname, ownername, id])
        else:
            query = __getQueryUpdateLastNetSynchIdForUserAndGroup(schema)
            #Logger.info(query)
            cursor.execute(query, [id, username, groupname, ownername])
        database.commit()
        return True
    except:
        Logger.info("Error: unable to insert net synch changes")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    return False

#############
## QUERIES ##
#############

#net_synch_changes

def __getQuerySelectNetSynchMediasFromPlayerSince(schema):
    """Creates a query for selecting entries originating from the player newer than a given id,
    i.e. where the value of the column "source" is PLAYER and the id is higher than the given one.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select * from " + schema + "." + TABLE_NET_SYNCH_CHANGES
    query += " where " + COLUMN_NSC_ID + " > %s"
    query += " and " + COLUMN_NSC_SOURCE + " = %s"
    return query

def __getQuerySelectMediaIdsProcessedSince(schema):
    """Creates a query for selecting entries that were processed after a given timestamp,
    i.e. where the value of the column "timestamp" is higher than a given one and the value of the column "processed" is 1.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select " + COLUMN_NSC_MEDIA_ID + " from " + schema + "." + TABLE_NET_SYNCH_CHANGES
    query += " where " + COLUMN_NSC_TIMESTAMP + " >= %s"
    query += " and " + COLUMN_NSC_PROCESSED + " = 1"
    return query

def __getQuerySelectMediasFromNetworkProcessedSince(schema):
    """Creates a query for selecting entries originating from the network, that were processed and newer than a given id,
    i.e. where the value of the column "source" is NETWORK, the value of the column "processed" is 1
    and the value of the column "id" is higher than the given one.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select * from " + schema + "." + TABLE_NET_SYNCH_CHANGES
    query += " where " + COLUMN_NSC_PROCESSED + " = 1"
    query += " and " + COLUMN_NSC_SOURCE + " = %s"
    query += " and " + COLUMN_NSC_ID + " > %s"
    return query

def __getQuerySelectLastUpdateProcessedTimestamp(schema):
    """Creates a query for selecting the highest value of the column "timestamp" of all entries in the NetSynchChanges table
    that have the given group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select max(" + COLUMN_NSC_TIMESTAMP + ")" 
    query += " from " + schema + "." + TABLE_NET_SYNCH_CHANGES
    query += " where " + COLUMN_NSC_GROUPNAME + " = %s"
    query += " and " + COLUMN_NSC_OWNERNAME + " = %s"
#    query += " and " + COLUMN_NSC_PROCESSED + " = 1"
    return query

def __getQuerySelectNearestNetSynchIdForTimestamp(schema):
    """Creates a query for selecting from the NetSynchChanges table the value of the column id
    where the value of the column timestamp is nearest to the given timestamp.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select min(" + COLUMN_NSC_ID + ") from " + schema + "." + TABLE_NET_SYNCH_CHANGES
    query += " where " + COLUMN_NSC_TIMESTAMP + " >= %s"
    return query

def __getQuerySelectNetSynchIdForGroupAndSource(schema):
    """Creates a query for selecting from the NetSynchChanges table the value of the column "id"
    that corresponds to the given group name and owner name and where the value of the column "source" is INITIAl.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select " + COLUMN_NSC_ID + " from " + schema + "." + TABLE_NET_SYNCH_CHANGES
    query += " where " + COLUMN_NSC_GROUPNAME + " = %s"
    query += " and " + COLUMN_NSC_OWNERNAME + " = %s"
    query += " and " + COLUMN_NSC_SOURCE + " = %s"
    return query

def __getQuerySelectLastNetSynchId(schema):
    """Creates a query for selecting the highest value of the column "id" of the table NetSynchChanges.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select max(" + COLUMN_NSC_ID + ") from " + schema + "." + TABLE_NET_SYNCH_CHANGES
    return query

def __getQueryInsertNetSynchChange(schema):
    """Creates a query for inserting an entry into the NetSynchChange table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "insert into " + schema + "." + TABLE_NET_SYNCH_CHANGES
    query += " (" + COLUMN_NSC_MEDIA_ID + "," + COLUMN_NSC_GROUPNAME + "," + COLUMN_NSC_OWNERNAME + ","
    query += COLUMN_NSC_USERNAME + "," + COLUMN_NSC_TIMESTAMP + "," + COLUMN_NSC_PROCESSED + ","
    query += COLUMN_NSC_SOURCE + "," + COLUMN_NSC_RELEVANT + ")"
    query += " values (%s,%s,%s,%s,%s,%s,%s,%s)"
    return query

def __getQuerySetRelevantToFalse(schema):
    """Creates a query for setting the value of the column "relevant" to 0 for all entries.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "update " + schema + "." + TABLE_NET_SYNCH_CHANGES
    query += " set " + COLUMN_NSC_RELEVANT + " = 0"
    return query
    
    
# last_netsynch

def __getQuerySelectLastNetSynchIdForUserAndGroup(schema):
    """Creates a query for selecting from the LastNetSynch table the value of the column last_netsynch_id
    that corresponds to the given user name, group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select " + COLUMN_LASTNS_LAST_NETSYNCH_ID + " from " + schema + "." + TABLE_USER_LAST_NETSYNCH
    query += " where " + COLUMN_LASTNS_USERNAME + " = %s"
    query += " and " + COLUMN_LASTNS_GROUPNAME + " = %s"
    query += " and " + COLUMN_LASTNS_OWNERNAME + " = %s"
    return query

def __getQueryInsertLastNetSynchIdForUserAndGroup(schema):
    """Creates a query for inserting in the LastNetSynch table the given id as last_netsynch_id
    for the given user name, group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "insert into " + schema + "." + TABLE_USER_LAST_NETSYNCH
    query += " (" + COLUMN_LASTNS_USERNAME + "," + COLUMN_LASTNS_GROUPNAME + ","
    query += COLUMN_LASTNS_OWNERNAME + "," + COLUMN_LASTNS_LAST_NETSYNCH_ID + ")"
    query += " values (%s,%s,%s,%s)"
    return query

def __getQueryUpdateLastNetSynchIdForUserAndGroup(schema):
    """Creates a query for updating in the LastNetSynch table the given id as last_netsynch_id
    for the given user name, group name and owner name.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "update " + schema + "." + TABLE_USER_LAST_NETSYNCH
    query += " set " + COLUMN_LASTNS_LAST_NETSYNCH_ID + " = %s"
    query += " where " + COLUMN_LASTNS_USERNAME + " = %s"
    query += " and " + COLUMN_LASTNS_GROUPNAME + " = %s"
    query += " and " + COLUMN_LASTNS_OWNERNAME + " = %s"
    return query

    
    
